using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Xml;

namespace Common.Database
{
    /// <summary>
    /// 
    /// </summary>
    [Serializable]
    public class PagedTableRecords
    {
        private string _TableName;
        private List<TableColumn> _Columns;
        private DataTable _Data;
        //private int _PageNum;
        //private int _RecordsPerPage;
        //private int _TotalRecords;

        #region ctor
        /// <summary>
        /// 
        /// </summary>
        /// <param name="tblKey"></param>
        /// <param name="pkFieldName"></param>
        /// <param name="pageNum"></param>
        /// <param name="recordsPerPage"></param>
        public PagedTableRecords(DbTableKey tblKey, string pkFieldName, int pageNum, int recordsPerPage)
        {
            this._TableName = tblKey.TblName;
            this._Columns =
                this.ToColumnList(
                    DbUtil.GetTableColumns(tblKey.DbProvider, tblKey.ConnStr, tblKey.SchemaName, tblKey.TblName));
            this.Data = DbUtil.GetPagedTableData(tblKey.ConnStr, tblKey.TblName, pkFieldName, pageNum, recordsPerPage);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connStr"></param>
        /// <param name="storedProcName"></param>
        /// <param name="tableName"></param>
        /// <param name="sqlParams"></param>
        /// <param name="pageNum"></param>
        /// <param name="recordsPerPage"></param>
        public PagedTableRecords(string connStr, string storedProcName, string tableName, SqlParameter[] sqlParams, int pageNum, int recordsPerPage)
        {
            this._TableName = tableName;
            this._Columns =
                this.ToColumnList(DbUtil.GetTableColumnsFromSelectionStoredProcedure(connStr, storedProcName, sqlParams));
            this.Data = DbUtil.GetPagedTableData(connStr, storedProcName, sqlParams, pageNum, recordsPerPage);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="xDoc"></param>
        /// <param name="elementName"></param>
        /// <param name="xPath"></param>
        /// <param name="pageNum"></param>
        /// <param name="recordsPerPage"></param>
        public PagedTableRecords(XmlDocument xDoc, string elementName, string xPath, int pageNum, int recordsPerPage)
        {
            this._TableName = elementName;
            XmlNodeList xNodes = xDoc.SelectNodes(xPath);
            this._Columns = null;
            this._Data = null;
            if (xNodes != null && xNodes.Count > 0 && xNodes[0].Attributes.Count>0)
            {
                // determine columns 
                this._Columns = new List<TableColumn>();
                this._Data = new DataTable();
                foreach(XmlAttribute att in xNodes[0].Attributes)
                {
                    this.AddColumn(att.Name);
                    this._Data.Columns.Add(att.Name, typeof(string));
                }
                int index = 0;
                int fromNumber = pageNum * recordsPerPage + 1;
                int toNumber = fromNumber + recordsPerPage;

                foreach(XmlNode xNode in xNodes)
                {
                    index++;
                    if(index>toNumber)
                        break;
                    if(index>=fromNumber)
                    {
                        DataRow drData = this._Data.NewRow();
                        foreach (XmlAttribute att in xNode.Attributes)
                        {
                            drData[att.Name] = att.Value;
                        }
                        this._Data.Rows.Add(drData);
                    }
                }
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="xDoc"></param>
        /// <param name="elementName"></param>
        /// <param name="elementPath"></param>
        /// <param name="keyAttName"></param>
        /// <param name="keyAttValue"></param>
        /// <param name="pageNum"></param>
        /// <param name="recordsPerPage"></param>
        public PagedTableRecords(XmlDocument xDoc, string elementName, string elementPath, string keyAttName, string keyAttValue, int pageNum, int recordsPerPage)
        {
            this._TableName = elementName;
            string xPath = elementPath + "[@" + keyAttName + "=" + keyAttValue + "]";
            XmlNodeList xNodes = xDoc.SelectNodes(xPath);
            this._Columns = null;
            this._Data = null;
            if (xNodes != null && xNodes.Count > 0 && xNodes[0].Attributes.Count > 0)
            {
                // determine columns 
                this._Columns = new List<TableColumn>();
                this._Data = new DataTable();
                foreach (XmlAttribute att in xNodes[0].Attributes)
                {
                    this.AddColumn(att.Name);
                    this._Data.Columns.Add(att.Name, typeof(string));
                }
                int index = 0;
                int fromNumber = pageNum * recordsPerPage + 1;
                int toNumber = fromNumber + recordsPerPage;

                foreach (XmlNode xNode in xNodes)
                {
                    index++;
                    if (index > toNumber)
                        break;
                    if (index >= fromNumber)
                    {
                        DataRow drData = this._Data.NewRow();
                        foreach (XmlAttribute att in xNode.Attributes)
                        {
                            drData[att.Name] = att.Value;
                        }
                        this._Data.Rows.Add(drData);
                    }
                }
            }
        }
        #endregion

        #region props
        /// <summary>
        /// 
        /// </summary>
        public string TableName
        {
            get { return _TableName; }
        }

        /// <summary>
        /// 
        /// </summary>
        public List<TableColumn> Columns
        {
            get { return _Columns; }
        }

        /// <summary>
        /// 
        /// </summary>
        public DataTable Data
        {
            get { return _Data; }
            set { _Data = value; }
        }
        #endregion

        #region cols
        private void AddColumn(string colName)
        {
            bool exist = false;
            if(this._Columns !=null && this._Columns.Count>0)
            {
                for (int i = 0; i < this._Columns.Count; i++)
                {
                    if(this._Columns[i].ColumnName.ToLower()==colName.ToLower())
                    {
                        exist = true;
                        break;
                    }
                }
            }
            if(!exist)
            {
                TableColumn col = new TableColumn();
                col.AllowNull = true;
                col.ColumnName = colName;
                col.DbType = SqlDbType.NVarChar;
                col.IsPK = false;
                col.IsPK = false;
                col.IsIndexed = false;
                col.IsIdentity = false;
                col.Ordinal = this._Columns.Count;
                col.Precision = 0;
                col.Scale = 0;
                col.Size = 0;
                this._Columns.Add(col);
            }
        }

        private List<TableColumn> ToColumnList(TableColumn[] cols)
        {
            List<TableColumn> colList = new List<TableColumn>();
            if(cols !=null && cols.Length>0)
            {
                foreach(TableColumn col in cols)
                {
                    colList.Add(col);
                }
            }
            return colList;
        }
        #endregion
    }
}
